# 作业：
# 1、类作业，统计镰刀和斧头的数量，要使用类方法

# class Tool():
#     count_liandao=0
#     count_fuzi=0
#     dict3={}
#     def __init__(self,name):
#         self.name=name
#         if self.name == "镰刀":
#             Tool.count_liandao +=1
#         elif self.name =="斧子":
#             Tool.count_fuzi +=1
#
#     @classmethod
#     def count(cls,name):
#         if name =="镰刀":
#             return cls.count_liandao
#         elif name =="斧子":
#             return cls.count_fuzi
#         else:
#             return "你好像在没事找事！"
#
# tool1=Tool('镰刀')
# tool2=Tool('镰刀')
# tool3=Tool('镰刀')
# tool4=Tool('斧子')
# tool5=Tool('斧子')
# tool6=Tool('斧子')
# tool7=Tool('斧子')
# print("镰刀:", Tool.count('镰刀'))
# print("斧子:", Tool.count('斧子'))


# class Tool():
#     count=0
#     tool_count = {}
#     def __init__(cls,name):
#         cls.name=name
#         Tool.count+=1
#         if name in Tool.tool_count:
#             Tool.tool_count[name]+=1
#         else:
#             Tool.tool_count[name]=1
#     @classmethod
#     def show_tool_count(cls):
#         return cls.count
#     @classmethod
#     def show_tool_counts(cls):
#         return cls.tool_count
# tool1=Tool('镰刀')
# tool2=Tool('斧头')
# tool3=Tool('镰刀')
# tool4=Tool('镰刀')
# tool_count = Tool.show_tool_count()
# tool_counts=Tool.show_tool_counts()
# print("工具数量:", tool_count)
# print("每个工具数量",tool_counts)


#
# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
# import pymysql

# oyt = pymysql.connect(user = "root",password = "020529",database="zjb",host="114.55.90.154")
# cursor = oyt.cursor()
# cursor.execute("select * from emp;")
# result = cursor.fetchall()
# print(result)
#
# import pymysql
#
#
# class DB():
#     def __init__(self, host, user, password, database, cursorclass=None):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.databases = database
#         self.cursorclass = cursorclass
#
#     def close(self):
#         self.cursor.close()  # 关闭游标
#         self.db.close()  # 关闭数据库
#
#     def connect(self):
#         self.db = pymysql.connect(host=self.host, user=self.user,
#                                   password=self.password, database=self.databases, cursorclass=self.cursorclass)
#         self.cursor = self.db.cursor()
#
#     def insert(self, sql):
#         self.connect()#连接
#         try:
#             self.cursor.execute(sql)#使用游标对象，执行sql语句
#             self.db.commit()#提交
#             result = '插入成功'
#         except Exception as e:
#             print('插入失败', e)
#             result = None
#         return result
#
#     def select_one(self, sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             result = self.cursor.fetchone()#查询一条
#             self.close()
#         except:
#             print('查询失败')
#             result = None
#         return result
#
#     def select_all(self, sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             result = self.cursor.fetchall()#查询所有
#             self.close()
#         except:
#             print('查询失败')
#             result = None
#         return result
#     def deleter(self,sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             result = '删除成功'
#         except Exception as e:
#             print('删除失败', e)
#             result = None
#         return result
#     def update(self,sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             result='修改成功'
#         except Exception as e:
#             print('失败！',e)
#             result=None
#         return result
#
# class Sunji(DB):
#     def suijichouqu(self, sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             result = self.cursor.fetchone()
#             self.close()
#         except Exception as e:
#             print('查询失败')
#             result = None
#         return f"{result['name']}到"



# d = DB('60.205.168.23', 'root', '000426', 'yys', pymysql.cursors.DictCursor)
# print(d.insert("insert into linyu(name,aze,address,tel) values('yys',25,'深渊',13840)"))
# print(d.insert("insert into linyu(name,aze,address,tel) values('yys',25,'深渊',13840)"))
# print(d.deleter("delete from linyu where id=1"))
# print(d.update("update linyu set address='提瓦特' where id=1 "))
# print(d.select_one('select * from linyu where id = 1'))
# result=d.select_all("select * from linyu")
# print(result)
#
# class Class_manage(DB):
#     def create1(self, sql):
#         self.create_table(sql)
#
#     def select_text(self, sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             result = self.cursor.fetchall()
#
#             self.close()
#         except Exception as e:
#             print('查询失败')
#             result = None
#         return result
#
#
# c1 = Class_manage('60.205.168.23', 'root', '000426', 'yys', pymysql.cursors.DictCursor)
# import sqlite3
# import random
#
# class Student:
#     def __init__(self, name, age, address, phone):
#         self.name = name
#         self.age = age
#         self.address = address
#         self.phone = phone
#
# class StudentManager:
#     def __init__(self, db_path):
#         self.db_path = db_path
#
#     def create_table(self):
#         conn = sqlite3.connect(self.db_path)
#         cursor = conn.cursor()
#         cursor.execute('''CREATE TABLE IF NOT EXISTS students
#                           (name TEXT, age INTEGER, address TEXT, phone TEXT)''')
#         conn.commit()
#         conn.close()
#
#     def add_student(self, student):
#         conn = sqlite3.connect(self.db_path)
#         cursor = conn.cursor()
#         cursor.execute("INSERT INTO students VALUES (?, ?, ?, ?)",
#                        (student.name, student.age, student.address, student.phone))
#         conn.commit()
#         conn.close()
#         print(f"{student.name} 存入成功")
#
#     def update_student(self, name, field, value):
#         conn = sqlite3.connect(self.db_path)
#         cursor = conn.cursor()
#         cursor.execute(f"UPDATE students SET {field} = ? WHERE name = ?", (value, name))
#         conn.commit()
#         conn.close()
#
#     def delete_student(self, name):
#         conn = sqlite3.connect(self.db_path)
#         cursor = conn.cursor()
#         cursor.execute("DELETE FROM students WHERE name = ?", (name,))
#         conn.commit()
#         conn.close()
#
#     def get_all_students(self):
#         conn = sqlite3.connect(self.db_path)
#         cursor = conn.cursor()
#         cursor.execute("SELECT name FROM students")
#         results = cursor.fetchall()
#         conn.close()
#         return [result[0] for result in results]
#
#     def random_call(self):
#         students = self.get_all_students()
#         if students:
#             random_student = random.choice(students)
#             print(f"{random_student} 到！")
#         else:
#             print("学生列表为空")
#
# # 创建管理器并连接数据库
# manager = StudentManager("student.db")
# manager.create_table()
#
# # 存入学生
# student1 = Student("林梓萱", 18, "北京市", "1234567890")
# manager.add_student(student1)
#
# # 修改学生信息
# manager.update_student("林梓萱", "age", 20)

# 删除学生
# manager.delete_student("张三")

# 存入更多学生...

# 展示全部学生
# students = manager.get_all_students()
# print("全部学生姓名：", students)

# 学生点名
# manager.random_call()


# 3、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序


# 4、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
